#!/usr/bin/python
# -*- encoding: utf-8 -*-
'''
@File    :   
@Time    :   // ::
@Author  :   Malygos 
@Version :   1.0
@Contact :   751756061@qq.com
@WebSite :   https://gitee.com/malygos_l/python_homework
'''
# Start typing your code from here

from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建对象的基类:
Base = declarative_base()


# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'Message_Book'

    # 表的结构:
    id = Column(String(2), primary_key=True)
    message = Column(String(255))
    name = Column(String(20))
    date_time = Column(String(100))
    is_delete = Column(String(10))


# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://root:18311010@localhost:3306/test')
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

session = DBSession()

#添加
try:
    new_person = User(id='9', message='hi', name='jarvis', date_time='2020-06-26 01:13:45')
    session.add(new_person)
    session.commit()
    print("Successful!")
except Exception as e:
    raise e

#删除
try:
    session.query(User).filter(User.id == '9').delete()
    session.commit()
    print("Successful!")

except Exception as e:
    raise e

#修改
try:
    modify_person = session.query(User).get(4)
    modify_person.name = 'MALYGOS'
    session.commit()
    print("Successful!")
except Exception as e:
    raise  e

#查询
try:
    search = session.query(User).filter(User.id == '5').all()
    print("ID\tmessage\tname\tdate_time\t\tis_delete")
    for i in search:
        print("{0}\t{1}\t{2}\t{3}\t{4}".format(i.id, i.message, i.name, i.date_time, i.is_delete))
    print("Successful!")
except Exception as e:
    raise e


session.close()